#!/bin/bash
# File Name: count_data.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-09 10:51:04
# Function: 
#########################################################################
db_name="db_cmdb"
mysql_user="moshan"
mysql_passwd="xxxxxx"
ip_port_list="/home/moshan/cmdb/ip_port.list"
host_list="192.168.2.142"
mysql_port=3306
mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db_name}"
${mysql_comm} -NBe "select tsi.ip_app ,tdi.port from t_server_info tsi join t_db_instance tdi on tsi.server_id=tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id=tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id=tdhgm.ha_group_id;" 2>/dev/null|grep -v "^172"|awk '{print $1":"$2}' >${ip_port_list} 2>/dev/null
for ((i=1;i<=$(wc -l < ${ip_port_list});i++)) #i  in $(awk '{print }' <<< "${ip_port_list}")
do
    host_list="$(awk -F: '{print $1}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    mysql_port="$(awk -F: '{print $2}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    db_size="$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024) 'data size(MB)' from information_schema.tables where DATA_LENGTH is not null and INDEX_LENGTH is not null;" 2>/dev/null)"
    #mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024) 'data size(MB)' from information_schema.tables where DATA_LENGTH is not null and INDEX_LENGTH is not null;"
    echo "${host_list},${mysql_port},${db_size}"
done |awk -F, -v sum=0 '{sum += $NF} END {printf ("%.10fPB\n",sum/1024/1024/1024)}' >> /home/moshan/cmdb/count_data_res.log
